﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Text.RegularExpressions;

namespace AlphaSolutionsGP
{
	public partial class frmStudentProfile: Form
	{
        private bool toModify;
        private Student aStudent;

		public frmStudentProfile()
		{
            toModify = false;
            aStudent = new Student();
			InitializeComponent();
            generateId();

            //list of default values
            comboStatus.Text = "Provisional";
            txtPhoto.Text = "profile_pictures/default.jpg";
            comboLevel.SelectedIndex = 0;
            fillInCombo(comboCourseID, "course_Code", "COURSE"); //fill in the course code combo box   

            dtStudentGraduationDate.Format = DateTimePickerFormat.Custom;
            dtStudentGraduationDate.CustomFormat = "dd/MM/yyyy";

            changeGraduationDate(dtStudentEnrollment.Value);
            txtStudentUniversityEmail.Text = txtStudentID.Text + "@woodlands.ac.uk";

            //set the date of birth to a blank value as initial state
            dtStudentDob.Format = DateTimePickerFormat.Custom;
            dtStudentDob.MaxDate = DateTime.Now;
            dtStudentDob.Value = DateTime.Now;
            dtStudentDob.CustomFormat = " ";
            
		}

        public frmStudentProfile(Student s)
        {
            //a student "s" is being modified
            toModify = true;
            //copy that student to be modified
            aStudent = s;            
            InitializeComponent();           
            //display the "add qualification" button because student exists
            btnEditStudentQualification.Visible = true;
            btnRefreshQualifications.Visible = true;
            //display the "add grade" and the "refresh" button because student exists
            btnGradeRefresh.Visible = true;
            btnGrade.Visible = true;
            fillInCombo(comboCourseID, "course_Code", "COURSE"); //fill in the course code combo box  
            //display "s" values
            displayStudentData();
            
        }

        //this will auto generate the studentID if the student to be created is a new student
        //it will take the highest value in the db and increment by 1
        private void generateId()
        {
            int studentId = 20010000;
            MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
            try
            {
                connection.Open();
                
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT (student_ID) FROM STUDENT ORDER BY student_ID DESC LIMIT 1";
                studentId = int.Parse(cmd.ExecuteScalar().ToString());
                studentId++;
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Unable to generate student ID because there are no students in system- new ID is " + studentId);
            }
            
            finally
            {
                connection.Close();
                aStudent.ID = studentId;
                txtStudentID.Text = studentId.ToString();
                txtStudentID.Enabled = false;
                txtPassword.Focus();

            }
        }
        
        //filtering the qualification for the specific student
        private void displayQualification(string sID)
        { 
            alpha_dbStudentDataSet.QUALIFICATION.Clear();
            MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
            try
            {
                connection.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("Connection failed");
            }
            try
            {               
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM QUALIFICATION WHERE STUDENT_student_ID='" + sID + "'";
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(this.alpha_dbStudentDataSet.QUALIFICATION);
                da.Dispose();
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Unable to fill table due to the following error:\n\n" + ex.ToString());
            }
            finally
            {
                connection.Close();

            }
        }

        //filtering the grades for the specific student
        private void displayGrade(string sID)
        {
            alpha_dbStudentDataSet.RESULT.Clear();
            MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
            try
            {
                connection.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("Connection failed");
            }
            try
            {
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM RESULT WHERE STUDENT_student_ID='" + sID + "'";
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(this.alpha_dbStudentDataSet.RESULT);
                da.Dispose();
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Unable to fill table due to the following error:\n\n" + ex.ToString());
            }
            finally
            {
                connection.Close();

            }
        }

        //print the data of the student to be modified into the form
        private void displayStudentData()
        {
            //print student "s" data
            comboStatus.Text = aStudent.Status;
            txtStudentID.Text = aStudent.ID.ToString();
            comboCourseID.Text = aStudent.CourseCode.ToString();

            dtStudentEnrollment.MinDate = convertFromMySQLDate(aStudent.DateEnrolled);
            dtStudentEnrollment.Value = convertFromMySQLDate(aStudent.DateEnrolled);

            dtStudentGraduationDate.MinDate = convertFromMySQLDate(aStudent.DateGraduated);
            dtStudentGraduationDate.Value = convertFromMySQLDate(aStudent.DateGraduated);

            dtStudentDob.Value = convertFromMySQLDate(aStudent.DOB);

            txtNationality.Text = aStudent.Nationality;
            txtFirstName.Text = aStudent.FirstName;
            txtMiddleName.Text = aStudent.MiddleName;
            txtSurname.Text = aStudent.Surname;
            txtStudentPhone.Text = aStudent.TelephoneNumber;
            txtStudentMobile.Text = aStudent.MobileNumber;
            txtStudentPersonalEmail.Text = aStudent.EmailAddress;
            txtStudentUniversityEmail.Text = aStudent.AssignedEmail;
            txtStudentNonTermStreet.Text = aStudent.StreetAddress;
            txtStudentNonTermTown.Text = aStudent.HomeTown;
            txtStudentNonTermCity.Text = aStudent.City;
            txtStudentNonTermPostcode.Text = aStudent.PostCode;
            txtPassword.Text = aStudent.Password;
            comboLevel.Text = aStudent.Level.ToString();
            txtPhoto.Text = aStudent.ImageLocation;

            //retrieve qualifications for this particular student
            try
            {
                displayQualification(aStudent.ID.ToString());
            }
            catch (Exception e)
            {
                MessageBox.Show("Unable to display qualifications:\n\n" + e.ToString());
            }

            try
            {
                displayGrade(aStudent.ID.ToString());
            }
            catch (Exception e)
            {
                MessageBox.Show("Unable to display grades:\n\n" + e.ToString());
            }
        }

        //this event fires when a cell is clicked on on the qualifications datagrid at the bottom.
        private void qUALIFICATIONDataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex > 1) return;
            int qId = (int)qUALIFICATIONDataGridView[2, e.RowIndex].Value;
            string studentID = (string)qUALIFICATIONDataGridView[3, e.RowIndex].Value;
            string qType = (string)qUALIFICATIONDataGridView[4, e.RowIndex].Value;
            string qTitle = (string)qUALIFICATIONDataGridView[5, e.RowIndex].Value;
            string qSubject = (string)qUALIFICATIONDataGridView[6, e.RowIndex].Value;
            string qGrade = (string)qUALIFICATIONDataGridView[7, e.RowIndex].Value;
            DateTime qDateAchieved = (DateTime)qUALIFICATIONDataGridView[8, e.RowIndex].Value;

            Qualification theQualification = new Qualification(qId, qType, qTitle, qSubject, qGrade, qDateAchieved.ToString());
            if (e.ColumnIndex == 0)
            {
                //view
                frmQualificationView f = new frmQualificationView(theQualification);
                f.Show();
            }
            else if (e.ColumnIndex == 1)
            {
                //delete
                if (MessageBox.Show(this, "Are you sure you wish to delete this Qualification", "Deleting qualification...",
                     MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                    == DialogResult.Yes)
                {
                    theQualification.removeQualification();
                }
            }
        }

        private void rESULTDataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex > 1) return;
            int resultID = (int)rESULTDataGridView[2, e.RowIndex].Value;
            string sID = (string)rESULTDataGridView[3, e.RowIndex].Value;
            int assID = (int)rESULTDataGridView[4, e.RowIndex].Value;
            string dtSubmission = rESULTDataGridView[5, e.RowIndex].Value.ToString();
            string grade = rESULTDataGridView[6, e.RowIndex].Value.ToString();

            Grade theGrade = new Grade(resultID, sID, assID, dtSubmission, grade);
            if (e.ColumnIndex == 0)
            {
                //view
                frmGradeView f = new frmGradeView(theGrade);
                f.Show();
            }
            else if (e.ColumnIndex == 1)
            {
                //delete
                if (MessageBox.Show(this, "Are you sure you wish to delete this Grade", "Deleting grade...",
                     MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                    == DialogResult.Yes)
                {
                    theGrade.removeGrade(resultID.ToString());
                    //this.rESULTTableAdapter.Fill(this.alpha_dbStudentDataSet.RESULT);
                }
            }

        }
          
        //back button
        private void btnBack_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        //save button
        private void btnSave_Click(object sender, EventArgs e)
        {
            if (validate())
            {
                //run update here
                if (toModify)
                {
                    //new data is validated and returns true
                    aStudent.Status = comboStatus.Text;
                    aStudent.ID = int.Parse(txtStudentID.Text);
                    aStudent.CourseCode = comboCourseID.Text;
                    aStudent.DateEnrolled = dtStudentEnrollment.Text;
                    aStudent.DateGraduated = dtStudentGraduationDate.Text;
                    aStudent.DOB = dtStudentDob.Text;
                    aStudent.Nationality = txtNationality.Text;
                    aStudent.FirstName = txtFirstName.Text;
                    aStudent.MiddleName = txtMiddleName.Text;
                    aStudent.Surname = txtSurname.Text;
                    aStudent.TelephoneNumber = txtStudentPhone.Text;
                    aStudent.MobileNumber = txtStudentMobile.Text;
                    aStudent.EmailAddress = txtStudentPersonalEmail.Text;
                    aStudent.AssignedEmail = txtStudentUniversityEmail.Text;
                    aStudent.StreetAddress = txtStudentNonTermStreet.Text;
                    aStudent.HomeTown = txtStudentNonTermTown.Text;
                    aStudent.City = txtStudentNonTermCity.Text;
                    aStudent.PostCode = txtStudentNonTermPostcode.Text;
                    aStudent.Password = txtPassword.Text;
                    aStudent.Level = int.Parse(comboLevel.Text);
                    aStudent.ImageLocation = txtPhoto.Text;
                    //modify sql
                    aStudent.amendStudent();
                    MessageBox.Show("The account for " + aStudent.FirstName + " " + aStudent.Surname + " has been amended.", "Amendment Successful", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    this.Close();
                }
                else
                {
                    txtStudentUniversityEmail.Text = txtStudentID.Text + "@woodlands.ac.uk";
                    aStudent.Level = int.Parse(comboLevel.Text);
                    //new data is validated and returns true
                    aStudent.Status = comboStatus.Text;
                    aStudent.ID = int.Parse(txtStudentID.Text);
                    aStudent.CourseCode = comboCourseID.Text;
                    aStudent.DateEnrolled = dtStudentEnrollment.Text;
                    aStudent.DateGraduated = dtStudentGraduationDate.Text;
                    aStudent.DOB = dtStudentDob.Text;
                    aStudent.Nationality = txtNationality.Text;
                    aStudent.FirstName = txtFirstName.Text;
                    aStudent.MiddleName = txtMiddleName.Text;
                    aStudent.Surname = txtSurname.Text;
                    aStudent.TelephoneNumber = txtStudentPhone.Text;
                    aStudent.MobileNumber = txtStudentMobile.Text;
                    aStudent.EmailAddress = txtStudentPersonalEmail.Text;
                    aStudent.AssignedEmail = txtStudentUniversityEmail.Text;
                    aStudent.StreetAddress = txtStudentNonTermStreet.Text;
                    aStudent.HomeTown = txtStudentNonTermTown.Text;
                    aStudent.City = txtStudentNonTermCity.Text;
                    aStudent.PostCode = txtStudentNonTermPostcode.Text;
                    aStudent.ImageLocation = txtPhoto.Text;
                    aStudent.Password = txtPassword.Text;

                    if (txtMiddleName.Text == "")
                        aStudent.MiddleName = "";
                    if (txtStudentMobile.Text == "")
                        aStudent.MobileNumber = "";


                    //run sql to create new student
                    try
                    {
                        aStudent.createStudent();
                        MessageBox.Show("Student has been created", "Success", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                        this.Close();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("There are problems with creating that student. Here is the error message: " + ex.ToString());
                    }
                }
            }
            
        }

        /**
         * method to validate all fields within the form
         */
        private bool validate()
        {
            bool flag = true;
            string alertBlankFields = "";
            string alertMobileNumb = "";
            string alertLandlineNum = "";
            string alertPostcode = "";
            string alertFirstName = "";
            string alertMiddleName = "";
            string alertSurname = "";
            string alertNationality = "";
            string alertCity = "";
            string alertTown = "";
            string alertEmailAddress = "";

            if (comboCourseID.Text == "" || txtPassword.Text == "" || txtFirstName.Text == "" || txtSurname.Text == ""
                ||txtNationality.Text==""||txtStudentNonTermStreet.Text==""||txtStudentNonTermTown.Text==""
                ||txtStudentNonTermCity.Text=="")
            {
                alertBlankFields = "\n- All mandatory fields must not be blank";
                flag = false;
            }

            
            //postcode validation
                // mobile number validation
                Match matchPostcode = Regex.Match(txtStudentNonTermPostcode.Text.Trim(), @"^([A-PR-UWYZ0-9][A-HK-Y0-9][AEHMNPRTVXY0-9\s]?[ABEHMNPRVWXY0-9]? {1,2}[0-9][ABD-HJLN-UW-Z]{2}|GIR 0AA)$"); //check for valid postcode format
                if (!matchPostcode.Success || txtStudentNonTermPostcode.Text=="") //if no characters were found
                {
                    alertPostcode = "\n- The post code is not a valid postcode.";
                    flag = false;
                }


            // mobile number validation
            if (txtStudentMobile.Text != "")
            {
                Match matchMobile = Regex.Match(txtStudentMobile.Text.Trim(), @"^[0-9]+$"); //check for integer numbers only within the string 
                if (!matchMobile.Success) //if no characters were found
                {
                    alertMobileNumb = "\n- Mobile number wrong format.";
                    flag = false;
                }
            }

            // landline number validation
            if (txtStudentPhone.Text != "")
            {
                Match matchLandline = Regex.Match(txtStudentPhone.Text.Trim(), @"^[0-9]+$"); //check for integer numbers only within the string 
                if (!matchLandline.Success) //if no characters were found
                {
                    alertLandlineNum = "\n- Landline number wrong format.";
                    flag = false;
                }
            }

            //first name validation
                Match matchFirstName = Regex.Match(txtFirstName.Text.Trim(), @"^[a-zA-Z]+$"); //check for integer numbers only within the string 
                if (!matchFirstName.Success||txtFirstName.Text=="") //if no characters were found
                {
                    alertFirstName = "\n- First name invalid.";
                    flag = false;
                }

            //middle name validation
            if (txtMiddleName.Text != "")
            {
                Match matchMiddleName = Regex.Match(txtMiddleName.Text.Trim(), @"^[a-zA-Z]+$"); //check for integer numbers only within the string 
                if (!matchMiddleName.Success) //if no characters were found
                {
                    alertMiddleName = "\n- Middle name invalid.";
                    flag = false;
                }
            }

            //surname name validation
                Match matchSurname = Regex.Match(txtSurname.Text.Trim(), @"^[a-zA-Z]+$"); //check for integer numbers only within the string 
                if (!matchSurname.Success||txtSurname.Text=="") //if no characters were found
                {
                    alertSurname = "\n- Surname invalid.";
                    flag = false;
                }

            //nationality name validation
                Match matchNationality = Regex.Match(txtNationality.Text.Trim(), @"^[a-zA-Z]+$"); //check for integer numbers only within the string 
                if (!matchNationality.Success||txtNationality.Text=="") //if no characters were found
                {
                    alertNationality = "\n- Nationality invalid.";
                    flag = false;
                }

            //town name validation
                Match matchTown = Regex.Match(txtStudentNonTermTown.Text.Trim(), @"^[a-zA-Z]+$"); //check for integer numbers only within the string 
                if (!matchTown.Success||txtStudentNonTermTown.Text=="") //if no characters were found
                {
                    alertTown = "\n- Town invalid.";
                    flag = false;
                }

            //city name validation
                Match matchCity = Regex.Match(txtStudentNonTermCity.Text.Trim(), @"^[a-zA-Z]+$"); //check for integer numbers only within the string 
                if (!matchCity.Success||txtStudentNonTermCity.Text=="") //if no characters were found
                {
                    alertCity = "\n- City invalid.";
                    flag = false;
                }

            //email address validation
            if (txtStudentPersonalEmail.Text != "")
            {
                Match matchEmailAddress = Regex.Match(txtStudentPersonalEmail.Text.Trim(), @"^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,4})$"); //check for valid email address format
                if (!matchEmailAddress.Success) //if no characters were found
                {
                    alertCity = "\n- Email address wrong format.";
                    flag = false;
                }
            }


            if (!flag)
            {
                MessageBox.Show(alertBlankFields + alertFirstName + alertMiddleName + alertSurname + alertNationality + alertLandlineNum + alertMobileNumb + alertPostcode + alertTown + alertCity + alertEmailAddress, "Filling fields error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }


            return flag;
        }

        private void btnEditStudentQualification_Click(object sender, EventArgs e)
        {
            frmQualificationView f = new frmQualificationView(txtStudentID.Text);           
            f.Show();
        }


        private void btnRefreshQualifications_Click(object sender, EventArgs e)
        {
            displayQualification(aStudent.ID.ToString()); // refresh the RESULT data grid
        }


        private void btnGrade_Click(object sender, EventArgs e)
        {
            frmGradeView grade = new frmGradeView(txtStudentID.Text);
            grade.Show();
        }


        public static DateTime convertFromMySQLDate(string dt)
        {
            //string dateToString = Convert.ToString(date);
            DateTime firstFormatDate = DateTime.Parse(dt);
            string dateString = firstFormatDate.ToString("dd/MM/yyyy");
            DateTime finalFormatDate = DateTime.Parse(dateString);
            return finalFormatDate.Date;
        }


        private void dtStudentEnrollment_ValueChanged(object sender, EventArgs e)
        {

            DateTime temp;
            if (!toModify) //will only execute the statement when adding new staff
            {
                temp = dtStudentEnrollment.Value.Date;
                dtStudentGraduationDate.Value = new DateTime(dtStudentGraduationDate.Value.Year - dtStudentGraduationDate.Value.Year + (temp.Year + 3), 6, 30); //graduation date default                
                //dtStudentGraduationDate.MinDate = dtStudentGraduationDate.Value;
            }
        }

        //DINAMICALLY FILL IN A DROPDOWN MENU
        private void fillInCombo(ComboBox cb, string column, string table)
        {
            MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
            try
            {
                connection.Open();
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT " + column + " FROM " + table + ""; //select the biggest staff id
                MySqlDataReader myReader = cmd.ExecuteReader();
                string nameColumn = "" + column + "";
                while (myReader.Read())
                {
                    column = myReader.GetString(nameColumn);
                    cb.Items.Add(column);
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Unable to fill table due to the following error:\n\n" + ex.ToString());
            }

            finally
            {
                connection.Close();

            }
        }


        private void changeGraduationDate(DateTime date)
        {
            DateTime temp;
            if (!toModify) //will only execute the statement when adding new staff
            {
                temp = date.Date;
                dtStudentGraduationDate.MinDate = new DateTime(dtStudentGraduationDate.Value.Year - dtStudentGraduationDate.Value.Year + (temp.Year + 3), 6, 30); //graduation date default                                

            }
        }


        private void btnGradeRefresh_Click(object sender, EventArgs e)
        {
            displayGrade(aStudent.ID.ToString()); // refresh the RESULT data grid
        }

        private void dtStudentDob_ValueChanged(object sender, EventArgs e)
        {
            dtStudentDob.Format = DateTimePickerFormat.Custom;
            dtStudentDob.CustomFormat = "dd/MM/yyyy";
        }

             
	}
}
